package com.csi.dao.impl;

import com.csi.dao.UserDao;
import com.csi.domain.*;
import com.csi.pojo.PageBean;
import com.csi.utils.DruidUtils;
import com.csi.utils.MD5Tools;

import java.sql.*;
import java.sql.Date;
import java.util.*;

public class UserDaoImpl implements UserDao {
    @Override
    public User login(String loginAccount, String loginPassword) throws SQLException {
        Connection connection = DruidUtils.getConnection();


        String sql = """
                SELECT
                u.user_id,
                u.user_number,
                u.user_name,
                u.english_name,
                u.login_account,
                u.gender,
                u.home_address,
                u.phone,
                u.email,
                u.borndate,
                u.image_address,
                u.company_address,
                u.pay_password,
                u.balance,
                u.pay_limit,
                u.status,
                r.role_name,
                r.role_id,
                p.permission_id,
                p.permission_name
                FROM
                user u
                JOIN user_role ur ON u.user_id = ur.user_id
                JOIN role r ON ur.role_id = r.role_id
                JOIN role_permission rp ON r.role_id = rp.role_id
                JOIN permission p ON rp.permission_id = p.permission_id
                WHERE
                u.login_account = ?
                AND u.login_password = ?;
                """;


        PreparedStatement ps = connection.prepareStatement(sql);

        ps.setString(1, loginAccount);
        ps.setString(2, loginPassword);

        ResultSet rs = ps.executeQuery();

        User user = null;

        while (rs.next()) {
            if (user == null) {
                user = new User();
                user.setUserId(rs.getInt("user_id"));
                user.setUserNumber(rs.getString("user_number"));
                user.setUserName(rs.getString("user_name"));
                user.setEnglishName(rs.getString("english_name"));
                user.setLoginAccount(rs.getString("login_account"));
                user.setGender(rs.getString("gender"));
                user.setHomeAddress(rs.getString("home_address"));
                user.setPhone(rs.getString("phone"));
                user.setEmail(rs.getString("email"));
                user.setBorndate(rs.getDate("borndate"));
                user.setImageAddress(rs.getString("image_address"));
                user.setCompanyAddress(rs.getString("company_address"));
                user.setPayPassword(rs.getString("pay_password"));
                user.setBalance(rs.getDouble("balance"));
                user.setPayLimit(rs.getDouble("pay_limit"));
                user.setStatus(rs.getInt("status"));

                // 创建角色列表
                user.setRoles(new ArrayList<>());
            }

            int roleId = rs.getInt("role_id");
            Role role = null;
            for (Role r : user.getRoles()) {
                if (r.getRoleId() == roleId) {
                    role = r;
                    break;
                }
            }
            if (role == null) {
                role = new Role();
                role.setRoleId(roleId);
                role.setRoleName(rs.getString("role_name"));
                role.setPermissions(new ArrayList<>());
                user.getRoles().add(role);
            }

            int permissionId = rs.getInt("permission_id");
            Permission permission = new Permission();
            permission.setPermissionId(permissionId);
            permission.setPermissionName(rs.getString("permission_name"));
            role.getPermissions().add(permission);

        }

        DruidUtils.close(connection);

        return user;
    }

    @Override
    public String findSalt(String loginAccount) throws SQLException {
        Connection connection = DruidUtils.getConnection();

        PreparedStatement ps = connection.prepareStatement("SELECT salt FROM user WHERE login_account = ?");

        ps.setString(1, loginAccount);

        String salt = null;
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            salt = rs.getString("salt");
        }

        DruidUtils.close(connection);

        return salt;
    }

    @Override
    public List<User> findAll() throws SQLException {
        Connection connection = DruidUtils.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<User> userList = new ArrayList<>();

        String sql = """
                SELECT u.*, r.*, p.permission_id, p.permission_name, p.permission_desc
                FROM user u
                LEFT JOIN user_role ur ON u.user_id = ur.user_id
                LEFT JOIN role r ON ur.role_id = r.role_id
                LEFT JOIN role_permission rp ON r.role_id = rp.role_id
                LEFT JOIN permission p ON rp.permission_id = p.permission_id
                """;
        ps = connection.prepareStatement(sql);
        rs = ps.executeQuery();

        while (rs.next()) {
            User user = null;
            int userId = rs.getInt("user_id");
            for (User u : userList) {
                if (u.getUserId() == userId) {
                    user = u;
                    break;
                }
            }
            if (user == null) {
                user = new User();
                user.setUserId(userId);
                user.setUserNumber(rs.getString("user_number"));
                user.setUserName(rs.getString("user_name"));
                user.setEnglishName(rs.getString("english_name"));
                user.setLoginAccount(rs.getString("login_account"));
                user.setLoginPassword(rs.getString("login_password"));
                user.setGender(rs.getString("gender"));
                user.setHomeAddress(rs.getString("home_address"));
                user.setPhone(rs.getString("phone"));
                user.setEmail(rs.getString("email"));
                user.setBorndate(rs.getDate("borndate"));
                user.setImageAddress(rs.getString("image_address"));
                user.setCompanyAddress(rs.getString("company_address"));
                user.setPayPassword(rs.getString("pay_password"));
                user.setBalance(rs.getDouble("balance"));
                user.setPayLimit(rs.getDouble("pay_limit"));
                user.setStatus(rs.getInt("status"));
                List<Role> roleList = new ArrayList<>();
                user.setRoles(roleList);
                userList.add(user);
            }

            Role role = null;
            int roleId = rs.getInt("role_id");
            for (Role r : user.getRoles()) {
                if (r.getRoleId() == roleId) {
                    role = r;
                    break;
                }
            }
            if (role == null) {
                role = new Role();
                role.setRoleId(roleId);
                role.setRoleName(rs.getString("role_name"));
                role.setRoleDesc(rs.getString("role_desc"));
                List<Permission> permissionList = new ArrayList<>();
                role.setPermissions(permissionList);
                user.getRoles().add(role);
            }

            Permission permission = new Permission();
            permission.setPermissionId(rs.getInt("permission_id"));
            permission.setPermissionName(rs.getString("permission_name"));
            permission.setPermissionDesc(rs.getString("permission_desc"));
            role.getPermissions().add(permission);
        }

        DruidUtils.close(connection);

        return userList;
    }

    @Override
    public PageBean<User> findPage(int currentPageNumber) throws SQLException {
        Connection connection = DruidUtils.getConnection();

        PageBean<User> pageBean = new PageBean<>();
        pageBean.setCurrentPageNumber(currentPageNumber);

        String sql = """
                SELECT 
                    u.user_id, 
                    u.user_number, 
                    u.user_name, 
                    u.english_name, 
                    u.login_account, 
                    u.gender, 
                    u.home_address, 
                    u.phone, 
                    u.email, 
                    u.borndate, 
                    u.image_address, 
                    u.company_address, 
                    u.status, 
                    r.role_name, 
                    r.role_id 
                FROM user u 
                JOIN user_role ur ON u.user_id = ur.user_id 
                JOIN role r ON ur.role_id = r.role_id 
                WHERE r.role_id = 6 
                LIMIT ?, ?;
                """;

        PreparedStatement ps = connection.prepareStatement(sql);

        ps.setInt(1, (currentPageNumber - 1) * pageBean.getPerPageNumber());
        ps.setInt(2, pageBean.getPerPageNumber());

        ResultSet rs = ps.executeQuery();

        Map<Integer, User> map = new HashMap<>(); // 创建一个空的Map集合，用于存储用户信息和角色信息

        while (rs.next()) {
            int userId = rs.getInt("user_id");

            if (!map.containsKey(userId)) { // 如果Map集合中不存在该用户ID，则创建一个新的用户对象，并将其添加到Map集合中
                User user = new User();

                user.setUserId(userId);
                user.setUserNumber(rs.getString("user_number"));
                user.setUserName(rs.getString("user_name"));
                user.setEnglishName(rs.getString("english_name"));
                user.setLoginAccount(rs.getString("login_account"));
                user.setGender(rs.getString("gender"));
                user.setHomeAddress(rs.getString("home_address"));
                user.setPhone(rs.getString("phone"));
                user.setEmail(rs.getString("email"));
                user.setBorndate(rs.getDate("borndate"));
                user.setImageAddress(rs.getString("image_address"));
                user.setCompanyAddress(rs.getString("company_address"));
                user.setStatus(rs.getInt("status"));

                List<Role> roles = new ArrayList<>(); // 创建一个空的集合，用于存储用户的所有角色信息

                Role role = new Role();
                role.setRoleName(rs.getString("role_name"));
                role.setRoleId(rs.getInt("role_id"));
                roles.add(role); // 将角色信息添加到集合中

                user.setRoles(roles); // 将所有角色信息设置到用户对象中

                map.put(userId, user); // 将用户对象添加到Map集合中
            } else { // 如果Map集合中已经存在该用户ID，则将角色信息添加到该用户对象中
                User user = map.get(userId);

                List<Role> roles = user.getRoles(); // 获取用户的角色信息

                Role role = new Role();
                role.setRoleName(rs.getString("role_name"));
                role.setRoleId(rs.getInt("role_id"));
                roles.add(role); // 将角色信息添加到集合中

                user.setRoles(roles); // 将所有角色信息设置到用户对象中
            }
        }

        List<User> list = new ArrayList<>(map.values()); // 将Map集合中的所有用户对象转换为List集合

        pageBean.setDatas(list);

        ps = connection.prepareStatement("SELECT COUNT(*) FROM user"); // 总记录数

        rs = ps.executeQuery();

        if (rs.next()) {
            pageBean.setTotalRecords(rs.getInt(1));
        }

        DruidUtils.close(connection);

        return pageBean;
    }

    @Override
    public User findById(int userId) throws SQLException {
        Connection connection = connection = DruidUtils.getConnection();

        // 查询用户基本信息
        String sql = "SELECT * FROM user WHERE user_id = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, userId);
        ResultSet resultSet = preparedStatement.executeQuery();

        User user = null;
        if (resultSet.next()) {
            user = new User();
            user.setUserId(resultSet.getInt("user_id"));
            user.setUserNumber(resultSet.getString("user_number"));
            user.setUserName(resultSet.getString("user_name"));
            user.setEnglishName(resultSet.getString("english_name"));
            user.setLoginAccount(resultSet.getString("login_account"));
            user.setLoginPassword(resultSet.getString("login_password"));
            user.setGender(resultSet.getString("gender"));
            user.setHomeAddress(resultSet.getString("home_address"));
            user.setPhone(resultSet.getString("phone"));
            user.setEmail(resultSet.getString("email"));
            user.setBorndate(resultSet.getDate("borndate"));
            user.setImageAddress(resultSet.getString("image_address"));
            user.setCompanyAddress(resultSet.getString("company_address"));
            user.setPayPassword(resultSet.getString("pay_password"));
            user.setBalance(resultSet.getDouble("balance"));
            user.setPayLimit(resultSet.getDouble("pay_limit"));
            user.setCodePath(resultSet.getString("code_path"));
            user.setStatus(resultSet.getInt("status"));
        }

        // 查询用户角色信息
        if (user != null) {
            sql = "SELECT r.* FROM user_role ur JOIN role r ON ur.role_id = r.role_id WHERE ur.user_id = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, userId);
            resultSet = preparedStatement.executeQuery();

            List<Role> roles = new ArrayList<>();
            while (resultSet.next()) {
                Role role = new Role();
                role.setRoleId(resultSet.getInt("role_id"));
                role.setRoleName(resultSet.getString("role_name"));
                role.setRoleDesc(resultSet.getString("role_desc"));
                roles.add(role);
            }

            user.setRoles(roles);
        }

        // 查询用户权限信息
        if (user != null && user.getRoles() != null) {
            for (Role role : user.getRoles()) {
                sql = "SELECT p.* FROM role_permission rp JOIN permission p ON rp.permission_id = p.permission_id WHERE rp.role_id = ?";
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setInt(1, role.getRoleId());
                resultSet = preparedStatement.executeQuery();

                List<Permission> permissions = new ArrayList<>();
                while (resultSet.next()) {
                    Permission permission = new Permission();
                    permission.setPermissionId(resultSet.getInt("permission_id"));
                    permission.setPermissionName(resultSet.getString("permission_name"));
                    permission.setPermissionDesc(resultSet.getString("permission_desc"));
                    permissions.add(permission);
                }

                role.setPermissions(permissions);
            }
        }

        DruidUtils.close(connection);

        return user;
    }


    @Override
    public void updateStatus(int status, int userId) throws SQLException {
        Connection connection = DruidUtils.getConnection();

        // 冻结支付密码置空
        PreparedStatement ps = connection.prepareStatement("UPDATE user SET status = ?,pay_password = null WHERE user_id = ?;");

        ps.setInt(1, status);
        ps.setInt(2, userId);

        ps.executeUpdate();

        DruidUtils.close(connection);

    }

    @Override
    public User updatePassword(int userId, String password) throws SQLException {
        Connection connection = DruidUtils.getConnection();
        PreparedStatement preparedStatement = null;

        String sql = "UPDATE user SET login_password = ?, salt = ? WHERE user_id = ?";
        preparedStatement = connection.prepareStatement(sql);
        // 重新生成随机盐
        String salt = MD5Tools.getSalt();
        // 对密码进行加盐和加密
        String encryptedPassword = MD5Tools.MD5Encode(password + salt, "utf-8");
        // 设置参数
        preparedStatement.setString(1, encryptedPassword);
        preparedStatement.setString(2, salt);
        preparedStatement.setInt(3, userId);
        // 执行更新操作
        int rows = preparedStatement.executeUpdate();
        if (rows > 0) {
            DruidUtils.close(connection);
            // 更新成功，返回更新后的用户信息
            return findById(userId);
        } else {
            DruidUtils.close(connection);
            // 更新失败，返回null
            return null;
        }

    }

    @Override
    public void updateInfo(User user) throws SQLException {

        Connection connection = DruidUtils.getConnection();
        PreparedStatement preparedStatement = null;
        String sql = "UPDATE user SET user_name = ?, phone = ?, email = ?, borndate = ?, gender = ?,login_account= ? WHERE user_id = ?";
        preparedStatement = connection.prepareStatement(sql);
        // 设置参数
        preparedStatement.setString(1, user.getUserName());
        preparedStatement.setString(2, user.getPhone());
        preparedStatement.setString(3, user.getEmail());
        preparedStatement.setDate(4, new Date(user.getBorndate().getTime()));
        preparedStatement.setString(5, user.getGender());
        preparedStatement.setString(6, user.getPhone());
        preparedStatement.setInt(7, user.getUserId());

        // 执行更新操作
        preparedStatement.executeUpdate();

        DruidUtils.close(connection);
    }

    @Override
    public String findSalt(int userId) throws SQLException {

        Connection connection = DruidUtils.getConnection();

        PreparedStatement ps = connection.prepareStatement("SELECT salt FROM user WHERE user_id = ?");

        ps.setInt(1, userId);

        String salt = null;
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            salt = rs.getString("salt");
        }

        DruidUtils.close(connection);

        return salt;
    }

    @Override
    public User findByLoginAccount(String loginAccount) throws SQLException {
        Connection connection = DruidUtils.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;

        String sql = """
                SELECT u.*, r.*, p.permission_id, p.permission_name, p.permission_desc
                FROM user u
                LEFT JOIN user_role ur ON u.user_id = ur.user_id
                LEFT JOIN role r ON ur.role_id = r.role_id
                LEFT JOIN role_permission rp ON r.role_id = rp.role_id
                LEFT JOIN permission p ON rp.permission_id = p.permission_id
                WHERE u.login_account = ?
                """;
        ps = connection.prepareStatement(sql);
        ps.setString(1, loginAccount);
        rs = ps.executeQuery();

        User user = null;
        if (rs.next()) {
            user = new User();
            user.setUserId(rs.getInt("user_id"));
            user.setUserNumber(rs.getString("user_number"));
            user.setUserName(rs.getString("user_name"));
            user.setEnglishName(rs.getString("english_name"));
            user.setLoginAccount(rs.getString("login_account"));
            user.setLoginPassword(rs.getString("login_password"));
            user.setGender(rs.getString("gender"));
            user.setHomeAddress(rs.getString("home_address"));
            user.setPhone(rs.getString("phone"));
            user.setEmail(rs.getString("email"));
            user.setBorndate(rs.getDate("borndate"));
            user.setImageAddress(rs.getString("image_address"));
            user.setCompanyAddress(rs.getString("company_address"));
            user.setPayPassword(rs.getString("pay_password"));
            user.setBalance(rs.getDouble("balance"));
            user.setPayLimit(rs.getDouble("pay_limit"));
            user.setStatus(rs.getInt("status"));
            user.setRoles(new ArrayList<>());

            Role role = null;
            int roleId = rs.getInt("role_id");
            for (Role r : user.getRoles()) {
                if (r.getRoleId() == roleId) {
                    role = r;
                    break;
                }
            }
            if (role == null) {
                role = new Role();
                role.setRoleId(roleId);
                role.setRoleName(rs.getString("role_name"));
                role.setRoleDesc(rs.getString("role_desc"));
                role.setPermissions(new ArrayList<>());
                user.getRoles().add(role);
            }

            Permission permission = new Permission();
            permission.setPermissionId(rs.getInt("permission_id"));
            permission.setPermissionName(rs.getString("permission_name"));
            permission.setPermissionDesc(rs.getString("permission_desc"));
            role.getPermissions().add(permission);
        }

        DruidUtils.close(connection);

        return user;
    }

    @Override
    public void entry(Integer userId, Employee employee, List<Integer> roles) throws SQLException {
        Connection connection = DruidUtils.getConnection();

        String sql1 = "INSERT INTO user_role (user_id, role_id) VALUES (?, ?)";
        PreparedStatement ps = connection.prepareStatement(sql1);
        for (Integer role : roles) {
            ps.setInt(1, userId);
            ps.setInt(2, role);
            ps.addBatch();
        }
        ps.executeBatch();


        String sql2 = "INSERT INTO employee (employee_account, onboarding_date) VALUES (?, now())";
        ps = connection.prepareStatement(sql2, Statement.RETURN_GENERATED_KEYS);
        ps.setString(1, employee.getEmployeeAccount());
        ps.executeUpdate();

        // 获取自增长的主键值
        ResultSet rs = ps.getGeneratedKeys();
        if (rs.next()) {
            int employeeId = rs.getInt(1);
            employee.setEmployeeId(employeeId);
        }

        String sql3 = "INSERT INTO user_employee (user_id, employee_id) VALUES (?, ?)";
        ps = connection.prepareStatement(sql3);
        ps.setInt(1, userId);
        ps.setInt(2, employee.getEmployeeId());
        ps.executeUpdate();

        DruidUtils.close(connection);

    }

    @Override
    public String findLast() throws SQLException {
        Connection connection = DruidUtils.getConnection();

        /*
        使用 LIKE 条件筛选以 "U" 开头的用户编号。
        然后，使用 SUBSTRING() 函数截取 "U" 后面的数字部分，并使用 CAST() 函数将其转换为无符号整数。
        最后，按照转换后的数字降序排序，并使用 LIMIT 1 限制结果集只返回一行，即最后一位员工的员工号。
        */
        String sql = "SELECT user_number FROM user WHERE user_number LIKE 'U%' ORDER BY CAST(SUBSTRING(user_number, 2) AS UNSIGNED) DESC LIMIT 1; ";
        PreparedStatement ps = connection.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();

        String str = null;
        if (rs.next()) {
            str = rs.getString("user_number");
        }

        DruidUtils.close(connection);

        return str;
    }

    @Override
    public void uploadPath(String imageAddress, int userId) throws SQLException {

        Connection connection = DruidUtils.getConnection();
        PreparedStatement preparedStatement = null;
        String sql = "UPDATE user SET image_address = ? WHERE user_id = ?";
        preparedStatement = connection.prepareStatement(sql);
        // 设置参数
        preparedStatement.setString(1, imageAddress);
        preparedStatement.setInt(2, userId);

        // 执行更新操作
        preparedStatement.executeUpdate();

        DruidUtils.close(connection);
    }

    @Override
    public BindAccount queryBindAccount(String userNumber, String accountNumber) throws SQLException {
        Connection connection = DruidUtils.getConnection();

        String sql = "SELECT * FROM bind_account WHERE user_number=? AND account_number=?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, userNumber);
        ps.setString(2, accountNumber);
        ResultSet rs = ps.executeQuery();
        BindAccount bindaccount = null;
        if (rs.next()) {
            bindaccount = new BindAccount();
            bindaccount.setUserNumber(rs.getString("user_number"));
            bindaccount.setAccountNumber(rs.getString("account_number"));
            bindaccount.setAccountType(rs.getString("account_type"));
            bindaccount.setBindingTime(rs.getDate("binding_time"));
            bindaccount.setIsDefault(rs.getInt("is_default"));
        }
        DruidUtils.close(connection);
        return bindaccount;
    }

    @Override
    public HashMap<String, Object> queryInfo(String userNumber, String accountNumber) throws SQLException {

        Connection connection = DruidUtils.getConnection();

        // 获得用户信息
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        User user = null;

        String sql = "SELECT * FROM user WHERE user_number=?";
        pstmt = connection.prepareStatement(sql);
        pstmt.setString(1, userNumber);
        rs = pstmt.executeQuery();
        if (rs.next()) {
            user = new User();
            user.setUserNumber(rs.getString("user_number"));
            user.setUserName(rs.getString("user_name"));
            user.setEnglishName(rs.getString("english_name"));
            user.setLoginAccount(rs.getString("login_account"));
            user.setGender(rs.getString("gender"));
            user.setHomeAddress(rs.getString("home_address"));
            user.setPhone(rs.getString("phone"));
            user.setEmail(rs.getString("email"));
            user.setCompanyAddress(rs.getString("company_address"));
        }

        // 获得账户信息
        PreparedStatement pstmt2 = null;
        ResultSet rs2 = null;
        AccountDetails accountDetails = null;

        String sql2 = "SELECT * FROM account_details WHERE account_number=?";
        pstmt2 = connection.prepareStatement(sql2);
        pstmt2.setString(1, accountNumber);
        rs2 = pstmt2.executeQuery();
        if (rs2.next()) {
            accountDetails = new AccountDetails();
            accountDetails.setAccountNumber(rs2.getString("account_number"));
            accountDetails.setAccountStatus(rs2.getString("account_status"));
            accountDetails.setCurrencyCode(rs2.getString("currency_code"));
            accountDetails.setLedgerBalance(rs2.getDouble("ledger_balance"));
            accountDetails.setAvailableBalance(rs2.getDouble("available_balance"));
        }

        // 封装返回结果
        HashMap<String, Object> map = new HashMap<>();
        map.put("user", user);
        map.put("account", accountDetails);

        DruidUtils.close(connection);

        return map;
    }

    @Override
    public double queryBalance(String userNumber) throws SQLException {

        Connection conn = DruidUtils.getConnection();

        PreparedStatement ps = null;
        ResultSet rs = null;
        double balance = 0;

        String sql = "SELECT balance FROM user WHERE user_number = ?";
        ps = conn.prepareStatement(sql);
        ps.setString(1, userNumber);
        rs = ps.executeQuery();
        if (rs.next()) {
            balance = rs.getDouble("balance");
        }

        DruidUtils.close(conn);

        return balance;
    }

    @Override
    public double queryPayLimit(String userNumber) throws SQLException {

        Connection conn = DruidUtils.getConnection();

        PreparedStatement ps = null;
        ResultSet rs = null;
        double payLimit = 0;

        String sql = "SELECT pay_limit FROM user WHERE user_number = ?";
        ps = conn.prepareStatement(sql);
        ps.setString(1, userNumber);
        rs = ps.executeQuery();
        if (rs.next()) {
            payLimit = rs.getDouble("pay_limit");
        }

        DruidUtils.close(conn);

        return payLimit;
    }

    @Override
    public List<AccountDetails> queryAccountList(String userNumber) throws SQLException {

        Connection conn = DruidUtils.getConnection();

        PreparedStatement ps = null;
        ResultSet rs = null;
        List<AccountDetails> list = new ArrayList<>();

        String sql = "SELECT * FROM account_details WHERE user_number = ?";
        ps = conn.prepareStatement(sql);
        ps.setString(1, userNumber);
        rs = ps.executeQuery();
        while (rs.next()) {
            AccountDetails accountDetails = new AccountDetails();
            accountDetails.setAccountNumber(rs.getString("account_number"));
            accountDetails.setAccountStatus(rs.getString("account_status"));
            accountDetails.setCurrencyCode(rs.getString("currency_code"));
            accountDetails.setLedgerBalance(rs.getDouble("ledger_balance"));
            accountDetails.setAvailableBalance(rs.getDouble("available_balance"));
            accountDetails.setCardType(rs.getString("card_type"));

            list.add(accountDetails);
        }

        DruidUtils.close(conn);

        return list;
    }

    @Override
    public List<AccountCredDetails> queryCredAccountList(String userNumber) throws SQLException {

        Connection conn = DruidUtils.getConnection();

        PreparedStatement ps = null;
        ResultSet rs = null;
        List<AccountCredDetails> list = new ArrayList<>();

        String sql = "SELECT * FROM account_cred_details WHERE user_number = ?";
        ps = conn.prepareStatement(sql);
        ps.setString(1, userNumber);
        rs = ps.executeQuery();
        while (rs.next()) {
            AccountCredDetails accountCredDetails = new AccountCredDetails();
            accountCredDetails.setAccountNumber(rs.getString("account_number"));
            accountCredDetails.setAccountStatus(rs.getString("account_status"));
            accountCredDetails.setCurrencyCode(rs.getString("currency_code"));
            accountCredDetails.setApprovedLimit(rs.getDouble("approved_limit"));
            accountCredDetails.setCashAdvanceLimit(rs.getDouble("cash_advance_limit"));
            accountCredDetails.setUsedLimit(rs.getDouble("used_limit"));
            accountCredDetails.setAvailableLimit(rs.getDouble("available_limit"));

            list.add(accountCredDetails);
        }

        DruidUtils.close(conn);

        return list;
    }

    @Override
    public List<BindAccount> queryBindAccountList(String userNumber) throws SQLException {

        Connection conn = DruidUtils.getConnection();

        PreparedStatement ps = null;
        ResultSet rs = null;
        List<BindAccount> list = new ArrayList<>();

        String sql = "SELECT * FROM bind_account WHERE user_number = ? ";
        ps = conn.prepareStatement(sql);
        ps.setString(1, userNumber);
        rs = ps.executeQuery();
        while (rs.next()) {
            BindAccount bindaccount = new BindAccount();
            bindaccount.setAccountNumber(rs.getString("account_number"));
            bindaccount.setAccountType(rs.getString("account_type"));
            bindaccount.setBindingTime(rs.getDate("binding_time"));
            bindaccount.setIsDefault(rs.getInt("is_default"));

            list.add(bindaccount);
        }

        DruidUtils.close(conn);

        return list;
    }

    @Override
    public void transferByWallet(String userNumber, String targetUserNumber, double transferAmount) throws SQLException {
        Connection connection = DruidUtils.getConnection();

        String sql1 = "UPDATE user SET balance = balance - ? WHERE user_number = ?";

        PreparedStatement ps1 = connection.prepareStatement(sql1);

        ps1.setDouble(1, transferAmount);
        ps1.setString(2, userNumber);

        ps1.executeUpdate();

        String sql2 = "UPDATE user SET balance = balance + ? WHERE user_number = ?";

        PreparedStatement ps2 = connection.prepareStatement(sql2);

        ps2.setDouble(1, transferAmount);
        ps2.setString(2, targetUserNumber);

        ps2.executeUpdate();

        DruidUtils.close(connection);
    }

    @Override
    public String queryPayPassword(String userNumber) throws SQLException {

        Connection conn = DruidUtils.getConnection();

        PreparedStatement ps = null;
        ResultSet rs = null;
        String payPassword = null;

        String sql = "SELECT pay_password FROM user WHERE user_number = ?";
        ps = conn.prepareStatement(sql);
        ps.setString(1, userNumber);
        rs = ps.executeQuery();
        if (rs.next()) {
            payPassword = rs.getString("pay_password");
        }

        DruidUtils.close(conn);

        return payPassword;

    }

    @Override
    public void updatePayPassword(String userNumber, String password) throws SQLException {

        Connection conn = DruidUtils.getConnection();

        PreparedStatement ps = null;

        String sql = "UPDATE user SET pay_password = ? WHERE user_number = ?";
        ps = conn.prepareStatement(sql);
        ps.setString(1, password);
        ps.setString(2, userNumber);
        ps.executeUpdate();

        DruidUtils.close(conn);
    }

    @Override
    public void updatePayLimit(String userNumber, Double payLimit) throws SQLException {

        Connection conn = DruidUtils.getConnection();

        PreparedStatement ps = null;

        String sql = "UPDATE user SET pay_limit = ? WHERE user_number = ?";
        ps = conn.prepareStatement(sql);
        ps.setDouble(1, payLimit);
        ps.setString(2, userNumber);
        ps.executeUpdate();

        DruidUtils.close(conn);
    }

    @Override
    public AccountCredDetails queryAccountCredDetails(String userNumber, String accountNumber) throws SQLException {
        Connection connection = DruidUtils.getConnection();

        String sql = "SELECT * FROM account_cred_details WHERE user_number=? AND account_number=?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, userNumber);
        ps.setString(2, accountNumber);
        ResultSet rs = ps.executeQuery();
        AccountCredDetails accountCredDetails = null;
        if (rs.next()) {
            accountCredDetails = new AccountCredDetails();
            accountCredDetails.setUserNumber(rs.getString("user_number"));
            accountCredDetails.setAccountNumber(rs.getString("account_number"));
            accountCredDetails.setAccountStatus(rs.getString("account_status"));
            accountCredDetails.setCurrencyCode(rs.getString("currency_code"));
            accountCredDetails.setApprovedLimit(rs.getDouble("approved_limit"));
            accountCredDetails.setCashAdvanceLimit(rs.getDouble("cash_advance_limit"));
            accountCredDetails.setUsedLimit(rs.getDouble("used_limit"));
            accountCredDetails.setAvailableLimit(rs.getDouble("available_limit"));
        }
        DruidUtils.close(connection);

        return accountCredDetails;
    }

    @Override
    public void payByWallet(String userNumber, String targetUserNumber, double amount) throws SQLException {

        Connection connection = DruidUtils.getConnection();

        String sql1 = "UPDATE user SET balance = balance - ? WHERE user_number = ?";

        PreparedStatement ps1 = connection.prepareStatement(sql1);

        ps1.setDouble(1, amount);
        ps1.setString(2, userNumber);

        ps1.executeUpdate();

        String sql2 = "UPDATE user SET balance = balance + ? WHERE user_number = ?";

        PreparedStatement ps2 = connection.prepareStatement(sql2);

        ps2.setDouble(1, amount);
        ps2.setString(2, targetUserNumber);

        ps2.executeUpdate();

        DruidUtils.close(connection);

    }

    @Override
    public void payByAccount(String accountNumber, String targetUserNumber, double amount) throws SQLException {

        Connection connection = DruidUtils.getConnection();

        String sql1 = "UPDATE account_details SET available_balance = available_balance - ? ,ledger_balance = ledger_balance - ? WHERE account_number = ?";

        PreparedStatement ps1 = connection.prepareStatement(sql1);

        ps1.setDouble(1, amount);
        ps1.setDouble(2, amount);
        ps1.setString(3, accountNumber);

        ps1.executeUpdate();

        String sql2 = "UPDATE user SET balance = balance + ? WHERE user_number = ?";

        PreparedStatement ps2 = connection.prepareStatement(sql2);

        ps2.setDouble(1, amount);
        ps2.setString(2, targetUserNumber);

        ps2.executeUpdate();

        DruidUtils.close(connection);
    }

    @Override
    public void payByCredAccount(String accountNumber, String targetUserNumber, double amount) throws SQLException {

        Connection connection = DruidUtils.getConnection();

        String sql1 = "UPDATE account_cred_details SET available_limit = available_limit - ?, userd_limit = userd_limit + ? WHERE account_number = ?";

        PreparedStatement ps1 = connection.prepareStatement(sql1);

        ps1.setDouble(1, amount);
        ps1.setDouble(2, amount);
        ps1.setString(3, accountNumber);

        ps1.executeUpdate();

        String sql2 = "UPDATE user SET balance = balance + ? WHERE user_number = ?";

        PreparedStatement ps2 = connection.prepareStatement(sql2);

        ps2.setDouble(1, amount);
        ps2.setString(2, targetUserNumber);

        ps2.executeUpdate();

        DruidUtils.close(connection);
    }

    @Override
    public double queryAvailableBalance(String userNumber) throws SQLException {

        Connection conn = DruidUtils.getConnection();

        PreparedStatement ps = null;
        ResultSet rs = null;
        double availableBalance = 0;

        String sql = "SELECT available_balance FROM account_details WHERE user_number = ?";
        ps = conn.prepareStatement(sql);
        ps.setString(1, userNumber);
        rs = ps.executeQuery();
        if (rs.next()) {
            availableBalance = rs.getDouble("available_balance");
        }

        DruidUtils.close(conn);

        return availableBalance;

    }

    @Override
    public double queryAvailableLimit(String userNumber) throws SQLException {

        Connection conn = DruidUtils.getConnection();

        PreparedStatement ps = null;
        ResultSet rs = null;
        double availableLimit = 0;

        String sql = "SELECT available_limit FROM account_cred_details WHERE user_number = ?";
        ps = conn.prepareStatement(sql);
        ps.setString(1, userNumber);
        rs = ps.executeQuery();
        if (rs.next()) {
            availableLimit = rs.getDouble("available_limit");
        }

        DruidUtils.close(conn);

        return availableLimit;

    }

    @Override
    public void bindCard(String userNumber, String accountNumber, String accountType) throws SQLException {
        Connection connection = DruidUtils.getConnection();

        String sql = "INSERT INTO bind_account(user_number,account_number,account_type,binding_time) VALUES (?,?,?,now())";

        PreparedStatement ps = connection.prepareStatement(sql);

        ps.setString(1, userNumber);
        ps.setString(2, accountNumber);
        ps.setString(3, accountType);

        ps.executeUpdate();

        DruidUtils.close(connection);

    }

    @Override
    public void unbindCard(String userNumber, String accountNumber) throws SQLException {
        Connection connection = DruidUtils.getConnection();

        String sql = "DELETE FROM bind_account WHERE user_number = ? AND account_number = ?;";

        PreparedStatement ps = connection.prepareStatement(sql);

        ps.setString(1, userNumber);
        ps.setString(2, accountNumber);

        ps.executeUpdate();

        DruidUtils.close(connection);
    }

    @Override
    public Integer register(User user, String salt) throws SQLException {
        Connection connection = DruidUtils.getConnection();
        // 向user表中插入数据
        String sql = "INSERT INTO user (user_number, user_name, login_account, salt,gender, login_password,phone, email, borndate) VALUES (?, ?, ?, ?, ?, ?, ?, ?,?)";
        PreparedStatement pstmt1 = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        pstmt1.setString(1, user.getUserNumber());
        pstmt1.setString(2, user.getUserName());
        pstmt1.setString(3, user.getLoginAccount());
        pstmt1.setString(4, salt);
        pstmt1.setString(5, user.getGender());
        pstmt1.setString(6, user.getLoginPassword());
        pstmt1.setString(7, user.getPhone());
        pstmt1.setString(8, user.getEmail());
        pstmt1.setDate(9, new Date(user.getBorndate().getTime()));

        pstmt1.executeUpdate();

        // 获取自动生成的主键值
        ResultSet rs = pstmt1.getGeneratedKeys();
        int userId = 0;
        if (rs.next()) {
            userId = rs.getInt(1);
            user.setUserId(userId);
        }

        // 向user_role表中插入数据
        String sql2 = "INSERT INTO user_role (user_id,role_id) VALUES (?, ?)";
        PreparedStatement pstmt2 = connection.prepareStatement(sql2);

        if (userId != 0) {
            pstmt2.setInt(1, userId);
        }
        pstmt2.setInt(2, 6);
        pstmt2.executeUpdate();

        DruidUtils.close(connection);

        return userId;

    }

    @Override
    public void recharge(String userNumber, String accountNumber, double amount) throws SQLException {

        Connection connection = DruidUtils.getConnection();

        String sql1 = "UPDATE user SET balance = balance + ? WHERE user_number = ?";

        PreparedStatement ps1 = connection.prepareStatement(sql1);

        ps1.setDouble(1, amount);
        ps1.setString(2, userNumber);

        ps1.executeUpdate();

        String sql2 = "UPDATE account_details SET ledger_balance = ledger_balance - ? ,available_balance = available_balance - ? WHERE account_number = ?";

        PreparedStatement ps2 = connection.prepareStatement(sql2);

        ps2.setDouble(1, amount);
        ps2.setDouble(2, amount);
        ps2.setString(3, accountNumber);

        ps2.executeUpdate();

        DruidUtils.close(connection);
    }

    @Override
    public void cashOut(String userNumber, String accountNumber, double amount) throws SQLException {

        Connection connection = DruidUtils.getConnection();

        String sql1 = "UPDATE user SET balance = balance - ? WHERE user_number = ?";

        PreparedStatement ps1 = connection.prepareStatement(sql1);

        ps1.setDouble(1, amount);
        ps1.setString(2, userNumber);

        ps1.executeUpdate();

        String sql2 = "UPDATE account_details SET ledger_balance = ledger_balance + ? ,available_balance = available_balance + ? WHERE account_number = ?";

        PreparedStatement ps2 = connection.prepareStatement(sql2);

        ps2.setDouble(1, amount);
        ps2.setDouble(2, amount);
        ps2.setString(3, accountNumber);

        ps2.executeUpdate();

        DruidUtils.close(connection);

    }

    @Override
    public void collectMoney(String userNumber, String targetUserNumber, double amount) throws SQLException {

        Connection connection = DruidUtils.getConnection();

        String sql1 = "UPDATE user SET balance = balance + ? WHERE user_number = ?";

        PreparedStatement ps1 = connection.prepareStatement(sql1);

        ps1.setDouble(1, amount);
        ps1.setString(2, userNumber);

        ps1.executeUpdate();

        String sql2 = "UPDATE user SET balance = balance - ? WHERE user_number = ?";

        PreparedStatement ps2 = connection.prepareStatement(sql2);

        ps2.setDouble(1, amount);
        ps2.setString(2, targetUserNumber);

        ps2.executeUpdate();

        DruidUtils.close(connection);
    }

    @Override
    public void updateAllInfo(User user) throws SQLException {
        Connection connection = DruidUtils.getConnection();
        PreparedStatement preparedStatement = null;
        String sql = "UPDATE user SET phone = ?, email = ?, borndate = ?, gender = ?,home_address=?,company_address=?,login_account=?,english_name=? WHERE user_id = ?";
        preparedStatement = connection.prepareStatement(sql);
        // 设置参数
        preparedStatement.setString(1, user.getPhone());
        preparedStatement.setString(2, user.getEmail());
        preparedStatement.setDate(3, new Date(user.getBorndate().getTime()));
        preparedStatement.setString(4, user.getGender());
        preparedStatement.setString(5, user.getHomeAddress());
        preparedStatement.setString(6, user.getCompanyAddress());
        preparedStatement.setString(7, user.getPhone());
        preparedStatement.setString(8, user.getEnglishName());
        preparedStatement.setInt(9, user.getUserId());

        // 执行更新操作
        preparedStatement.executeUpdate();

        DruidUtils.close(connection);
    }

    @Override
    public void forgotPassword(String phone, String loginPassword) throws SQLException {
        Connection connection = DruidUtils.getConnection();

        String sql = "UPDATE user SET login_password = ?, salt = ? WHERE phone = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        // 重新生成随机盐
        String salt = MD5Tools.getSalt();
        // 对密码进行加盐和加密
        String encryptedPassword = MD5Tools.MD5Encode(loginPassword + salt, "utf-8");
        // 设置参数
        preparedStatement.setString(1, encryptedPassword);
        preparedStatement.setString(2, salt);
        preparedStatement.setString(3, phone);
        // 执行更新操作
        preparedStatement.executeUpdate();

        DruidUtils.close(connection);

    }

    @Override
    public Integer queryUserIdByPhone(String phone) throws SQLException {
        Connection connection = DruidUtils.getConnection();

        String sql = "SELECT user_id FROM user WHERE phone = ?";

        PreparedStatement ps = connection.prepareStatement(sql);

        ps.setString(1, phone);

        ResultSet rs = ps.executeQuery();

        Integer userId = null;
        if (rs.next()) {
            userId = rs.getInt("user_id");
        }

        DruidUtils.close(connection);

        return userId;
    }


    @Override
    public User findUserByPhone(String phone) throws SQLException {

        Connection connection = DruidUtils.getConnection();

        String sql = "SELECT user_id FROM user WHERE phone = ?";

        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, phone);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            int userId = rs.getInt("user_id");
            User user = new User();
            user.setUserId(userId);
            return user;
        } else {
            return null;
        }
    }

    @Override
    public void uploadCodePath(String codePath, int userId) throws SQLException {

        Connection connection = DruidUtils.getConnection();
        PreparedStatement preparedStatement = null;
        String sql = "UPDATE user SET code_path = ? WHERE user_id = ?";
        preparedStatement = connection.prepareStatement(sql);
        // 设置参数
        preparedStatement.setString(1, codePath);
        preparedStatement.setInt(2, userId);

        // 执行更新操作
        preparedStatement.executeUpdate();

        DruidUtils.close(connection);
    }

}

